var express = require("express"); //引入express模块
const bodyParser = require("body-parser"); //引入body-parser模块
var mysql = require("mysql"); //引入mysql模块
var app = express(); //创建express的实例
const cors = require("cors");
const primery = "SmartCompus";

app.use(cors());
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    //使用body-parser中间件
    extended: false
  })
);

let db = mysql.createConnection({
  //创建mysql实例
  host: "127.0.0.1",
  port: "3306",
  user: "root",
  password: "18869002231k",
  database: "spyp"
});
db.connect();

//视频
//查询
app.get("/api/VideoUpload", (req, res) => {
  db.query("SELECT * FROM sp_wxckzl_mtk1", (err, data) => {
    if (err) {
      console.log(err);
      res.status(500).send("数据库有问题了");
    } else {
      if (data) {
        console.log(JSON.parse(JSON.stringify(data)));
        res.send({
          data: JSON.parse(JSON.stringify(data))
        });
        console.log("finsh Select")
      } else {
        console.log("数据访问失败");
        res.send("数据更新失败");
      }
    }
  });
});

//确认
app.post("/api/VideoUpload", (req, res) => {
  db.query("SELECT MAX(SPMSBH) AS maxPX FROM sp", (err, data) => {
    var change1_1 = JSON.parse(JSON.stringify(data));
    var ids1 = Number(change1_1[0].maxPX) + 1;
    db.query("SELECT MAX(MTKBH) AS maxPX FROM wxckzl_mtk1", (err, data) => {
      var change1_2 = JSON.parse(JSON.stringify(data));
      var ids2 = Number(change1_2[0].maxPX) + 1;

      db.query(
        `INSERT INTO sp  VALUES ('${ids1}','${req.body.SPMSLX}','${req.body.SPMSMC}','${req.body.SPGJZ}','${req.body.SPMSJJ}','${req.body.SPMSFMTPURL}','${req.body.SPMSDJS}','${req.body.SPMSZT}','${req.body.SPMSFBSJ}','${req.body.SPMSFBR}')`,
        (err, data) => {

          db.query(
            `INSERT INTO wxckzl_mtk1  VALUES ('${ids2}','${ids1}','0000','${req.body.MTKURL}','${req.body.MTKZT}','${req.body.MTKSCSJ}','${req.body.MTKFBR}')`,
            (err, data) => {

            if (err) {
              console.log(err);
              res.status(500).send("数据库有问题了");
            } else {
              console.log("上传成功");
              res.send("上传成功");
            }
          });
      });
    });
  });
});

//删除
app.delete(`/api/VideoUpload/:ID`, (req, res) => {
  db.query(`DELETE FROM sp WHERE SPMSBH = ${req.params.ID}`, (err, data) => {
    db.query(`DELETE FROM wxckzl_mtk1 WHERE MSBH = ${req.params.ID}`, (err, data) => {
      if (err) {
        console.log(err);
        res.status(500).send("数据库有问题了");
      } else {
        console.log("删除成功");
        res.send({
          data: true
        });
      }
    });
  });
});

//修改
app.post("/api/VideoModify", (req, res) => {
  console.log(req.body);
  var ID = req.params.SPMSBH
  db.query(
    `UPDATE sp SET SPMSLX = '${req.body.SPMSLX}',SPMSMC = '${req.body.SPMSMC}',SPMSJJ = '${req.body.SPMSJJ}',SPMSFMTPURL = '${req.body.SPMSFMTPURL}',SPMSZT = '${req.body.SPMSZT}',SPGJZ = '${req.body.SPGJZ}' WHERE SPMSBH = '${ID}'`,
    (err, data) => {
      db.query(
        `UPDATE wxckzl_mtk1 SET MTKURL = '${req.body.MTKURL}',MTKZT = '${req.body.MTKZT}' WHERE MSBH = '${ID}'`,
        (err, data) => {
          if (err) {
            console.log(err);
            res.status(500).send("数据库有问题了");
          } else {
            if (data) {
              console.log(JSON.parse(JSON.stringify(data)));

              res.send({
                data: true
              });
            } else {
              console.log("数据访问失败");
              res.send("数据更新失败");
            }
          }
      });
  });
});

//音频
//查询
app.get("/api/AudioFrequency", (req, res) => {
  db.query("SELECT * FROM yp_wxckzl_mtk2 ", (err, data) => {
    if (err) {
      console.log(err);
      res.status(500).send("数据库有问题了");
    } else {
      if (data) {
        console.log(JSON.parse(JSON.stringify(data)));
        res.send({
          data: JSON.parse(JSON.stringify(data))
        });
        console.log("finsh Select")
      } else {
        console.log("数据访问失败");
        res.send("数据更新失败");
      }
    }
  });
});

//确认
app.post("/api/AudioFrequency", (req, res) => {
  db.query("SELECT MAX(YPMSBH) AS maxPX FROM yp", (err, data) => {
    var change2_1 = JSON.parse(JSON.stringify(data));
    var ids1 = Number(change2_1[0].maxPX) + 1;
    db.query("SELECT MAX(MTKBH) AS maxPX FROM wxckzl_mtk2", (err, data) => {
      var change2_2 = JSON.parse(JSON.stringify(data));
      var ids2 = Number(change2_2[0].maxPX) + 1;

      db.query(
        `INSERT INTO yp  VALUES ('${ids1}','${req.body.YPMSLX}','${req.body.YPMSMC}','${req.body.YPGJZ}','${req.body.YPMSFMTPURL}','${req.body.YPMSJJ}','${req.body.YPMSDJS}','${req.body.YPMSZT}','${req.body.YPMSFBSJ}','${req.body.YPMSFBR}')`,
        (err, data) => {

          db.query(
            `INSERT INTO wxckzl_mtk2  VALUES ('${ids2}','${ids1}','0000','${req.body.MTKURL}','${req.body.MTKZT}','${req.body.MTKSCSJ}','${req.body.MTKFBR}')`,
            (err, data) => {

            if (err) {
              console.log(err);
              res.status(500).send("数据库有问题了");
            } else {
              console.log("上传成功");
              res.send("上传成功");
            }
          });
      });
    });
  });
});

//删除
app.delete(`/api/AudioFrequency/:ID`, (req, res) => {
  db.query(`DELETE FROM yp WHERE YPMSBH = ${req.params.ID}`, (err, data) => {
    db.query(`DELETE FROM wxckzl_mtk2 WHERE MSBH = ${req.params.ID}`, (err, data) => {
      if (err) {
        console.log(err);
        res.status(500).send("数据库有问题了");
      } else {
        console.log("删除成功");
        res.send({
          data: true
        });
      }
    });
  });
});

//修改
app.post("/api/AudioModify", (req, res) => {
  console.log(req.body);
  var ID = req.params.YPMSBH
  db.query(
    `UPDATE yp SET SPMSLX = '${req.body.YPMSLX}',SPMSMC = '${req.body.YPMSMC}',SPMSJJ = '${req.body.YPMSJJ}',SPMSFMTPURL = '${req.body.YPMSFMTPURL}',SPMSZT = '${req.body.YPMSZT}',SPGJZ = '${req.body.YPGJZ}' WHERE SPMSBH = '${ID}'`,
    (err, data) => {
      db.query(
        `UPDATE wxckzl_mtk2 SET MTKURL = '${req.body.MTKURL}',MTKZT = '${req.body.MTKZT}' WHERE MSBH = '${ID}'`,
        (err, data) => {
          if (err) {
            console.log(err);
            res.status(500).send("数据库有问题了");
          } else {
            if (data) {
              console.log(JSON.parse(JSON.stringify(data)));

              res.send({
                data: true
              });
            } else {
              console.log("数据访问失败");
              res.send("数据更新失败");
            }
          }
      });
  });
});

app.get("/", function(req, res) {
  res.send("请勿访问！！！！"); ////服务器响应请求
});

app.listen(3000, function() {
  ////监听3000端口
  console.log("Server running at 3000 port");
});
